1. Field of the Invention
The invention relates generally to database systems of the kind which store metadata in catalogs or data dictionaries and more specifically to techniques for reading and manipulating the metadata.
2. Description of Related Art
FIG. 1 provides an overview of the invention disclosed herein. A preferred embodiment of the invention is implemented in an improved version of the Oracle8(trademark) Server, manufactured by Oracle Corporation, Redwood City, Calif. The Oracle8 server includes an object-relational database system. The object-relational database system appears in FIG. 1 as DBMS 103; prior to modification as required for the invention, DBMS 103 includes database 108, together with those tables 107 in data dictionary 106 that are required to implement database 108.
Information is stored in DBMS 103 as objects. In FIG. 1, all objects have the reference number 105. Objects are divided into classes according to the kind of information they contain. Shown in FIG. 1 are objects of the TABLE class 141, the TRIGGER class 143, the INDEX class 145, the UDT (user-defined type) class 147, and the VIEW class 149. Many other classes of objects exist as well in DBMS 103. Object classes that are of particular importance for the present discussion are TABLE class 141, UDT class 147, and VIEW class 149. DBMS 103 is a relational database system. In such systems, most of the data is stored in objects of TABLE class 141. As implied by the name, an object of TABLE class 141 contains a table. The table is made up of rows and columns. Each row has a field for each of the columns. A column specifies a kind of data value and a row represents an entity which has values of the kinds specified by the column. For example, in a table for storing personal information, the columns might be called last_name, first_name, m_i, street_addr, and so forth, with each row representing a person and the fields in the row having the appropriate values for that person.
DBMS 103 is an object relational database system. In such a system, the data values in a column may be objects which have user-defined types or UDTs. For instance, a user might define a type called contact_info which contained fields for all of the contact information for an individual and a column in a table of persons might specify a field for which the values are objects of type contact_info. Objects of class UDT are objects with user-defined types.
The objects of the TABLE class are what is termed base tables, that is, the information contained in an object of the TABLE class is stored as a table in DBMS 103""s memory system. DBMS 103 has other tables which use the information contained in one or more base tables, but do not make separate copies of the information. These tables are termed views and the objects that represent views are objects of VIEW class 149. Views can be used to combine information from a number of different base tables and even other views. A kind of view which is of particular interest in the present discussion is an object view. An object view is a view that is associated with a user-defined type. Each row in the table specified by the object view is an object of the user-defined type associated with the object view. The information which the object view obtains from the base tables either directly or via other views is mapped into fields of the objects which make up the rows in the view.
The objects in DBMS 103 belong either to data dictionary 106 or to database 108. The information in data dictionary 106 is metadata, that is, information that defines all of the objects in DBMS 103, those in database 108, and also those in data dictionary 106. In the Oracle8 server, most of the objects in data dictionary 106 are TABLE objects 105 that belong to system tables 107. For example, there is a table named tab$ in system tables 107 that contains a row for every table defined in DBMS 103, including the tables in system tables 107.
A problem with present-day database systems is that there is no simple way of obtaining all of the metadata that describes an object 105 in DBMS 103. For example, to obtain a definition of a TABLE object 141, a user or a client executing a program written by a user may have to make over a dozen queries to retrieve table, column, object, partition, sub-partition, tablespace, index, grant, storage, nested-table and owner information associated with the base table. Moreover, in order to select the views to query, the user must know what subtype of object one is dealing with (e.g., relational vs. object table, partitioned vs. non-partitioned table, index-organized vs. heap table, temporary vs. persistent table), i.e., in order to obtain the metadata for an object, the user must have a good understanding of the structure of data dictionary 106.
The difficulty of obtaining the metadata for an object adds to the expense and effort required to use and maintain DBMS 103. For example, in today""s networked environment, it is often useful to have partial copies of database 108 at different locations within the network. To make such a partial copy, one needs to extract the metadata from data dictionary 106 that defines the partial copy and then use that information to create the partial copy. Easy access to information about the logical structure of a database is of course also useful whenever one is modifying the database""s logical structure. In present-day database systems, a user who wishes to obtain a useful definition of an object in the database faces three main problems:
No Simple Means of Obtaining Complete Database Object Definitions
Present-day database systems provide no facility which permits a user to obtain the complete definition of any object in DBMS 103. The Oracle8 server provides an example of what is available in present-day systems for obtaining metadata from data dictionary 106. The Oracle8 server provides a series of views onto data dictionary 106, but, like the tables within the data dictionary itself, these views are normalized for efficiency; hence, several queries against multiple views need to be executed in order to retrieve the complete metadata for what is logically a single object in the database.
No Means to Perform Transformations on Database Object Definitions
Often, the reason for extracting a definition of an object is to transform it. For example, a client that is maintaining a local copy of a portion of a database generally wishes to perform transformations such as adding a column to a table, changing a table definition into a snapshot definition, changing object ownership, removing specific storage attributes, etc. Today, the client requires custom code to perform these sorts of transformations.
No Means to Generate Creation SQL DDL for Database Objects
Once an object""s definition is extracted and perhaps transformed, a client invariably wants to recreate that object somewhere; perhaps in another schema of the source database or perhaps in some other target database. In SQL databases such as that provided by the Oracle8 server, objects are created by defining them in a data definition language (DDL) and then providing the DDL to the database system, which creates the object as defined in the DDL. Present-day database systems provide no simple way of getting from the metadata for an object to the DDL needed to create another such object. At present, the client must use custom code to produce this DDL from the metadata for the object.
The absence of a facility which makes it easy to obtain complete and accurate metadata for objects in the database also to put the metadata into a form which makes it easy not only to modify the metadata, but also to produce creation DDL from the metadata has led to large amounts of duplicated effort within the organizations that produce database systems and even larger amounts of duplicated effort within the organizations that use the database systems. It is thus an object of the invention disclosed herein to provide a facility that makes extraction of metadata for objects from the data dictionary easy and further puts the metadata into a form such that it can be easily modified and easily used to create the objects they describe. It is further an object of the invention to provide techniques that generally simplify the aggregation of data that is stored in a plurality of objects in a database system.
The foregoing objects of the invention are achieved by apparatus for aggregating data stored in a number of objects. The apparatus associates a kind of data to be aggregated with a user-defined type that defines an object for containing the aggregated data and an object view that specifies the locations of the data to be aggregated and has an application program interface that includes a fetch interface that causes the database system to make a query over the object view associated with the kind of data to be aggregated that obtains the data to be aggregated from a plurality of objects and returns an object of the user-defined type associated with the kind that contains the aggregated data.
In another aspect of the invention, there are a number of different kinds of data to be aggregated. The apparatus associates a name with a kind of data to be aggregated and associates the user-defined type and the object view with the name. The fetch interface responds to a name of a kind of data by making a query over the object view associated with the name that returns and object of the user-defined type associated with the name. The application program interface may further include a filter interface that permits a client of the database system to specify a filter that restricts the query over the object view. The filter may also be associated with the name of the kind of data.
In a further aspect of the invention, the apparatus may convert the contents of the returned object of the user-defined type into an intermediate form. The application program may further include a transform interface that permits a client of the database system to specify a transformation of the intermediate form.
One application for the invention is retrieving metadata for objects belonging to a given class from the data base""s data dictionary. In this application, the user-defined type, the object view, and the filter are all associated with the name of the class. One of the transformations from the intermediate form is creation DDL for an object whose metadata has been retrieved by the apparatus. XML is a particularly useful intermediate form, with transformations being made using XSL stylesheets.